package com.itcast.utils;

import java.io.IOException;
import java.lang.reflect.Field;
import java.lang.reflect.InvocationTargetException;
import java.sql.*;
import java.util.ArrayList;
import java.util.List;
import java.util.Properties;

public class JDBCUtils02 {
    //创建一个Properties对象
    private static Properties properties = new Properties();
    static {
        // 加载配置文件，使用当前类的类加载器
        //getResourceAsStream()返回用于读取指定资源的输入流。
        //load()从输入字节流中读取属性列表（键和元素对）。
        try {
            properties.load(ClassLoader.getSystemClassLoader().getResourceAsStream("jdbc.properties"));
            //getProperty()在此属性列表中搜索具有指定键的属性。
            Class.forName(properties.getProperty("driverClassName"));
        } catch (Exception e) {
            throw new RuntimeException(e);
        }

    }
    /**
     * 获取连接
     *
     * @return
     * @throws SQLException
     */
    private static Connection getConnection() throws SQLException {
        Connection connection = DriverManager.getConnection(properties.getProperty("url"), properties.getProperty("username"), properties.getProperty("password"));
        return connection;
    }

    /**
     * 删除
     *
     * @return
     * @throws ClassNotFoundException
     * @throws SQLException
     */
    public int testDelete1(String sql, Object... args) throws ClassNotFoundException, SQLException {
        //使用JDBC操作数据库
        //2.获取链接
        Connection connection = getConnection();
        //3.获取执行者对象
        PreparedStatement pstmt = connection.prepareStatement(sql);
        //执行删除语句
        for (int i = 0; i < args.length; i++) {
            pstmt.setObject(i + 1, args[i]);
        }
        int row = pstmt.executeUpdate();
        if (row > 0) {
            System.out.println("删除成功");
        } else {
            System.out.println("删除失败");
        }
        //5.释放资源
        close(pstmt, connection);
        return row;
    }

    /**
     * 修改
     *
     * @param sql
     * @param args
     * @return响应结果数量
     * @throws ClassNotFoundException
     * @throws SQLException
     */
    public int testUpdate1(String sql, Object... args) throws ClassNotFoundException, SQLException, IOException {
        //使用JDBC操作数据库
        //2.获取链接
        Connection connection = getConnection();
        //3.获取执行者对象
        PreparedStatement statement = connection.prepareStatement(sql);

        for (int i = 0; i < args.length; i++) {
            statement.setObject(i + 1, args[i]);
        }
        /*statement.setObject(1,"小黄");
        statement.setObject(2,2);*/
        //4.告诉他sql语句去执行去 ,executeUpdate 本质执行DML语句
        int row = statement.executeUpdate();
        if (row > 0) {
            System.out.println("修改成功");
        } else {
            System.out.println("修改失败");
        }
        //5.释放资源
        close(statement, connection);
        return row;
    }
    /**
     * 添加
     *
     * @throws ClassNotFoundException
     * @throws SQLException
     */
    public int testInsert1(String sql,Object... args) throws ClassNotFoundException, SQLException {
        //使用JDBC操作数据库
        //2.获取链接
        Connection connection = getConnection();
        //3.获取执行者对象
        PreparedStatement statement = connection.prepareStatement(sql);
        for (int i = 0; i < args.length; i++) {
            statement.setObject(i + 1, args[i]);
        }
        //4.告诉他sql语句去执行去 ,executeUpdate 本质执行DML语句
        int row = statement.executeUpdate();
        if (row > 0) {
            System.out.println("添加成功");
        } else {
            System.out.println("添加失败");
        }
        //5.释放资源
        close(statement, connection);
        return row;
    }

    /**
     * 查询
     *
     * @param sql
     * @param clazz
     * @param
     * @param <T>
     * @return
     * @throws ClassNotFoundException
     * @throws SQLException
     */
    public static <T> List<T> testSelect1(String sql, Class<T> clazz, Object... args) throws ClassNotFoundException, SQLException, NoSuchMethodException, InvocationTargetException, InstantiationException, IllegalAccessException {
        //获取链接
        // Connection connection = DriverManager.getConnection("jdbc:mysql://localhost:3306/db02", "root", "1234");
        Connection connection = getConnection();
        //获取执行者对象
        PreparedStatement statement = connection.prepareStatement(sql);
//        for (int i = 0; i < args.length; i++) {
//            statement.setObject(i + 1, args[i]);
//        }
        //执行sql语句
        /*
            ResultSet
                next(); 将游标往下移动一行,并且返回改行是否有数据
                getXxx(String columnName); 根据列名获取列值
         */
        //获得结果集
        ResultSet rs = statement.executeQuery();
        //得到映射字段
        Field[] fields = clazz.getDeclaredFields();
        //创建集合用于存储结果集
        List<T> list = new ArrayList<T>();
        //遍历结果集
        while (rs.next()){
            //创建对象
            T t = clazz.getDeclaredConstructor().newInstance();
            //遍历对象字段给对象赋值
            for (Field field : fields) {
                //设置字段可访问
                field.setAccessible(true);
                //获取字段名
                String name = field.getName();
                //获取字段值
                Object value = rs.getObject(name);
                //给对象字段赋值
                field.set(t, value != null ? value.toString() : null);
            }
            list.add(t);
        }
        //释放资源
        close(statement, connection,rs);
        //返回结果集
        return list;
    }

    private static void close(PreparedStatement statement, Connection connection,ResultSet rs){
        try {
            statement.close();
            connection.close();
            rs.close();
        } catch (SQLException e) {
            throw new RuntimeException(e);
        }

    }
    private static void close(PreparedStatement statement, Connection connection){
        close(statement, connection, null);
    }
}
